##################################################################################################################
# Code for Results in Appendix Sections A1.3
##################################################################################################################
write.table(data.frame(Description=c("","","","Appendix Section A1.3: Direct Evidence From Federal Foreign Agents Registration Act (FARA) Disclosures")),row.names=F,col.names=F,quote=F)

##################################################################################################################
# To run, please set the working directory to match the location of the folder containing all replication files.
##################################################################################################################
#setwd("Replication_PSRM_ST24")

#if not yet created, create "output" folder
if("output"%in%list.files()==F){dir.create("output")}

#clear environment
rm(list=ls());gc();gc();gc();gc()

#if not yet installed, install packages
if(length(find.package("dplyr",quiet=T))==0){install.packages("dplyr")}
if(length(find.package("knitr",quiet=T))==0){install.packages("knitr")} 
if(as.numeric(available.packages()["knitr","Version"])<1.48){install.packages("knitr")} #updated version required to prevent error with kbl(x,format="latex)
if(length(find.package("kableExtra",quiet=T))==0){install.packages("kableExtra")}

#load packages
library(dplyr)
library(kableExtra)

#Set POSIX locale
Sys.setlocale(locale="C")

#top federal lobbying companies (2017 and 2018) from Open Secrets
l17<-read.csv("Data/Appendix_A1_3/OpenSecrets_TopLobbyists/Top Lobbying Firms_2017_OpenSecrets.csv",stringsAsFactors = F)
l18<-read.csv("Data/Appendix_A1_3/OpenSecrets_TopLobbyists/Top Lobbying Firms_2018_OpenSecrets.csv",stringsAsFactors = F)
l17$Total.Income<-as.numeric(gsub("\\$","",l17$Total.Income))
l18$Total.Income<-as.numeric(gsub("\\$","",l18$Total.Income))
l1718<-merge(l17,l18,by=c("Lobbying.Firm"),all=T)
l1718[is.na(l1718$Total.Income.x),c("Total.Income.x")]<-0
l1718[is.na(l1718$Total.Income.y),c("Total.Income.y")]<-0
l1718$Income<-l1718$Total.Income.x+l1718$Total.Income.y
l1718[order(l1718[,c("Income")],decreasing = T)[1:20],]
sum(l1718$income[order(l1718[,c("Income")],decreasing = T)[1:20]])
# 1                 Akin Gump et al       39080000       37705000 76785000
# 5          Brownstein Hyatt et al       28705000       31260000 59965000
# 4                       BGR Group       23670000       27224000 50894000
# 21            Squire Patton Boggs       24295000       24222000 48517000
# 15               Holland & Knight       22100000       23990000 46090000
# 9  Cornerstone Government Affairs       19210000       21800000 41010000
# 17                      K&L Gates       17690000       18330000 36020000
# 6                 Capitol Counsel       18240000       17640000 35880000
# 10            Covington & Burling       18013000       17005000 35018000
# 22               Van Scoyoc Assoc       17015000       16395000 33410000
# 23              Williams & Jensen       16630000       16220000 32850000
# 7            Capitol Tax Partners       14430000       14710000 29140000
# 18      Mehlman Castagnetti et al       14220000       14525000 28745000
# 19             Peck Madigan Jones       13880000       14170000 28050000
# 8                 Cassidy & Assoc       13720000       14320000 28040000
# 13    Fierce Government Relations       13190000       13190000 26380000
# 2      American Continental Group       12620000       13480000 26100000
# 20                  Podesta Group       18410000              0 18410000
# 3                Ballard Partners              0       18140000 18140000
# 12                  Ernst & Young       15160000              0 15160000

df0<-l1718[order(l1718[,c("Income")],decreasing = T)[1:20],c(1,4)]

#Assemble overview files for lobbying firms that are in above list and have FARA AB filings
fs<-list.files("Data/Appendix_A1_3/FARA/AB",recursive = T,full.names = T)
fs<-fs[grep("csv",fs)]
ov<-do.call(rbind,lapply(fs,function(x) read.csv(x,stringsAsFactors = F)))

#Get filings from 2017 and 2018
ov<-filter(ov,grepl("AB-2017",ov$DOCUMENT_URL)==T|grepl("AB-2018",ov$DOCUMENT_URL)==T)
write.table(data.frame(Description=c("","Number of filings in 2017 & 2018")),row.names=F,col.names=F,quote=F)
print(nrow(ov)) #80
#Exclude filed observations with no contract
ov<-ov[which(ov$DOCUMENT_URL%in%paste("https://efile.fara.gov/docs/",c("3492-Exhibit-AB-20181218-55.pdf",
                                  "5430-Exhibit-AB-20170111-59.pdf",
                                  "5870-Exhibit-AB-20170131-6.pdf",
                                  "6401-Exhibit-AB-20180831-5.pdf",
                                  "6331-Exhibit-AB-20180209-3.pdf"),sep="")==F),]

write.table(data.frame(Description=c("","Number of filings in 2017 & 2018 with contract")),row.names=F,col.names=F,quote=F)
print(nrow(ov)) #75
#Indicator for each observation
ov$filed_agreement<-1
#Code flat fee
ov$flat_fees<-as.numeric(ov$DOCUMENT_URL%in%paste("https://efile.fara.gov/docs/",c(
  "3492-Exhibit-AB-20171220-46.pdf",
  "3492-Exhibit-AB-20180315-49.pdf",
  "3492-Exhibit-AB-20180405-50.pdf",
  "3492-Exhibit-AB-20180417-51.pdf",
  "3492-Exhibit-AB-20180501-52.pdf",
  "3492-Exhibit-AB-20180718-53.pdf",
  "6598-Exhibit-AB-20181009-1.pdf",

  "6415-Exhibit-AB-20170405-1.pdf",
  "6415-Exhibit-AB-20170413-2.pdf",
  "6415-Exhibit-AB-20170519-3.pdf",
  "6415-Exhibit-AB-20170821-4.pdf",
  "6415-Exhibit-AB-20170828-5.pdf",
  "6415-Exhibit-AB-20171003-6.pdf",
  "6415-Exhibit-AB-20171222-7.pdf",
  "6415-Exhibit-AB-20180221-8.pdf",
  "6415-Exhibit-AB-20180402-9.pdf",
  "6415-Exhibit-AB-20180424-10.pdf",
  "6415-Exhibit-AB-20180613-11.pdf",
  "6415-Exhibit-AB-20180627-12.pdf",
  "6415-Exhibit-AB-20180629-14.pdf",
  "6415-Exhibit-AB-20180925-13.pdf",

  "5430-Exhibit-AB-20170101-58.pdf",
  "5430-Exhibit-AB-20170320-60.pdf",
  "5430-Exhibit-AB-20170327-61.pdf",
  "5430-Exhibit-AB-20170907-62.pdf",
  "5430-Exhibit-AB-20171120-63.pdf",
  "5430-Exhibit-AB-20171230-64.pdf",
  "5430-Exhibit-AB-20180501-65.pdf",
  "5430-Exhibit-AB-20180612-66.pdf",
  "5430-Exhibit-AB-20180626-67.pdf",
  "5430-Exhibit-AB-20180814-69.pdf",
  "5430-Exhibit-AB-20180814-70.pdf",

  "5870-Exhibit-AB-20170223-7.pdf",
  "5870-Exhibit-AB-20170719-8.pdf",
  "5870-Exhibit-AB-20171129-9.pdf",
  "5870-Exhibit-AB-20180803-10.pdf",

  "6328-Exhibit-AB-20170112-3.pdf",
  "6328-Exhibit-AB-20170915-4.pdf",
  "6328-Exhibit-AB-20180409-5.pdf",
  "6328-Exhibit-AB-20181119-6.pdf",

  "6272-Exhibit-AB-20170128-3.pdf",

  "6401-Exhibit-AB-20170120-1.pdf",
  "6401-Exhibit-AB-20170810-2.pdf",
  "6401-Exhibit-AB-20180228-3.pdf",
  "6401-Exhibit-AB-20180831-6.pdf",

  "3718-Exhibit-AB-20180406-9.pdf",

  "6331-Exhibit-AB-20170123-2.pdf",
  "6331-Exhibit-AB-20180219-4.pdf",
  "6331-Exhibit-AB-20180227-5.pdf",

  "5926-Exhibit-AB-20170120-92.pdf",
  "5926-Exhibit-AB-20170125-93.pdf",
  "5926-Exhibit-AB-20170223-94.pdf",
  "5926-Exhibit-AB-20170308-95.pdf",
  "5926-Exhibit-AB-20170309-96.pdf",
  "5926-Exhibit-AB-20170315-97.pdf",
  "5926-Exhibit-AB-20170327-98.pdf",
  "5926-Exhibit-AB-20170404-99.pdf",
  "5926-Exhibit-AB-20170406-100.pdf",
  "5926-Exhibit-AB-20170412-101.pdf",
  "5926-Exhibit-AB-20170731-102.pdf",
  "5926-Exhibit-AB-20170815-103.pdf",
  "5926-Exhibit-AB-20170817-104.pdf",

  "2165-Exhibit-AB-20170222-68.pdf",
  "2165-Exhibit-AB-20170601-69.pdf",
  "2165-Exhibit-AB-20180711-72.pdf"
),sep="")==T)

#code flat fee where some expenses are explicitly excluded
ov$flat_fees_excl_exp<-as.numeric(ov$DOCUMENT_URL%in%paste("https://efile.fara.gov/docs/",c(
  
  "3492-Exhibit-AB-20180315-49.pdf",
  "3492-Exhibit-AB-20180405-50.pdf",
  
  "6598-Exhibit-AB-20181009-1.pdf",
  
  "6415-Exhibit-AB-20170405-1.pdf",
  "6415-Exhibit-AB-20170413-2.pdf",
  "6415-Exhibit-AB-20170519-3.pdf",
  "6415-Exhibit-AB-20170828-5.pdf",
  "6415-Exhibit-AB-20180221-8.pdf",
  "6415-Exhibit-AB-20180402-9.pdf",
  "6415-Exhibit-AB-20180424-10.pdf",
  "6415-Exhibit-AB-20180613-11.pdf",
  "6415-Exhibit-AB-20180627-12.pdf",
  "6415-Exhibit-AB-20180629-14.pdf",
  "6415-Exhibit-AB-20180925-13.pdf",
  
  "5430-Exhibit-AB-20170320-60.pdf",
  "5430-Exhibit-AB-20170907-62.pdf",
  "5430-Exhibit-AB-20171120-63.pdf",
  "5430-Exhibit-AB-20180501-65.pdf",
  "5430-Exhibit-AB-20180612-66.pdf",
  "5430-Exhibit-AB-20180626-67.pdf",
  "5430-Exhibit-AB-20180814-69.pdf",
  "5430-Exhibit-AB-20180814-70.pdf",
  
  "5870-Exhibit-AB-20170223-7.pdf",
  "5870-Exhibit-AB-20180803-10.pdf",
  
  "6272-Exhibit-AB-20170128-3.pdf",
  
  "6401-Exhibit-AB-20170120-1.pdf",
  "6401-Exhibit-AB-20170810-2.pdf",
  "6401-Exhibit-AB-20180228-3.pdf",
  "6401-Exhibit-AB-20180831-6.pdf",
  
  "6331-Exhibit-AB-20170123-2.pdf",
  "6331-Exhibit-AB-20180219-4.pdf",
  "6331-Exhibit-AB-20180227-5.pdf",
  
  "5926-Exhibit-AB-20170223-94.pdf",
  "5926-Exhibit-AB-20170308-95.pdf",
  "5926-Exhibit-AB-20170315-97.pdf",
  "5926-Exhibit-AB-20170404-99.pdf",
  "5926-Exhibit-AB-20170412-101.pdf",
  "5926-Exhibit-AB-20170731-102.pdf",
  "5926-Exhibit-AB-20170815-103.pdf",

  "2165-Exhibit-AB-20170222-68.pdf",
  "2165-Exhibit-AB-20170601-69.pdf",
  "2165-Exhibit-AB-20180711-72.pdf"
),sep="")==T)

#code mention of retainers
ov$retainer<-as.numeric(ov$DOCUMENT_URL%in%paste("https://efile.fara.gov/docs/",c(
    "3492-Exhibit-AB-20170123-42.pdf",
    "3492-Exhibit-AB-20170825-43.pdf",
    "3492-Exhibit-AB-20170913-45.pdf",
    "3492-Exhibit-AB-20180720-54.pdf",
    "3492-Exhibit-AB-20170906-44.pdf",
  
  "6328-Exhibit-AB-20181210-7.pdf",
  
  "6331-Exhibit-AB-20180718-6.pdf",
  
    "2165-Exhibit-AB-20171002-70.pdf",
    "2165-Exhibit-AB-20180110-71.pdf"
),sep="")==T)

#code mention of hourly rates
ov$hourly_rate<-as.numeric(ov$DOCUMENT_URL%in%paste("https://efile.fara.gov/docs/",c(
  "3492-Exhibit-AB-20170906-44.pdf",
  "3492-Exhibit-AB-20180308-47.pdf",
  "2165-Exhibit-AB-20180110-71.pdf"
),sep="")==T)

#aggregate across lobbying firms
ov2<-ov%>%group_by(REGISTRANTNAME) %>%
  summarise(No_of_Agreements=sum(filed_agreement),Flat_Fees=sum(flat_fees),Flat_Fees_Excl_Exp=sum(flat_fees_excl_exp),
            Retainers=sum(retainer),Hourly_Rates=sum(hourly_rate))

#make names compatible for merging
ov2$Lobbying.Firm<-ov2$REGISTRANTNAME
ov2$Lobbying.Firm[which(ov2$REGISTRANTNAME=="Akin, Gump, Strauss, Hauer & Feld, LLP")]<-"Akin Gump et al"
ov2$Lobbying.Firm[which(ov2$REGISTRANTNAME=="BGR Government Affairs, LLC")]<-"BGR Group"
ov2$Lobbying.Firm[which(ov2$REGISTRANTNAME=="Brownstein Hyatt Farber Schreck, LLP")]<-"Brownstein Hyatt et al"
ov2$Lobbying.Firm[which(ov2$REGISTRANTNAME=="Capitol Counsel, LLC")]<-"Capitol Counsel"
ov2$Lobbying.Firm[which(ov2$REGISTRANTNAME=="Cassidy & Associates, Inc.")]<-"Cassidy & Assoc"
ov2$Lobbying.Firm[which(ov2$REGISTRANTNAME=="K&L Gates, LLP")]<-"K&L Gates"
ov2$Lobbying.Firm[which(ov2$REGISTRANTNAME=="Squire Patton Boggs, LLP")]<-"Squire Patton Boggs"
ov2$Lobbying.Firm[which(ov2$REGISTRANTNAME=="Podesta Group, Inc.")]<-"Podesta Group"

#Merge with LDA data from OpenSecrets
df<-merge(df0,ov2,by=c("Lobbying.Firm"),all.x=T)

#Replace NAs with 0
for(i in which(colnames(df)%in%c("No_of_Agreements","Flat_Fees","Flat_Fees_Excl_Exp","Retainers","Hourly_Rates"))){
  df[is.na(df[,i]),i]<-0
}
#Rename column
colnames(df)[which(colnames(df)=="Lobbying.Firm")]<-"Lobbying_Firm"
#Order columns
df<-df[,c("Lobbying_Firm","No_of_Agreements","Flat_Fees","Flat_Fees_Excl_Exp","Retainers","Hourly_Rates","Income")]
#Add summary row
df[21,]<-c("Total",sum(df$No_of_Agreements),sum(df$Flat_Fees),sum(df$Flat_Fees_Excl_Exp),sum(df$Retainers),sum(df$Hourly_Rates),sum(df$Income))
df$Flat_Fees<-paste(df$Flat_Fees," (",df$Flat_Fees_Excl_Exp,")",sep="")
df<-df[,which(colnames(df)%in%c("Flat_Fees_Excl_Exp")==F)]
colnames(df)[which(colnames(df)==c("Flat_Fees"))]<-c("Flat Fees (Exc. Exp.)")
colnames(df)[which(colnames(df)==c("No_of_Agreements"))]<-c("No. of Agreements")
colnames(df)[which(colnames(df)==c("Hourly_Rates"))]<-c("Hourly Rates")
colnames(df)[which(colnames(df)==c("Lobbying_Firm"))]<-c("Lobbying Firm")
#Make sure Income variable is numeric
df$Income<-as.numeric(df$Income)
colnames(df)[which(colnames(df)==c("Income"))]<-c("Total Income '17-'18 (LDA)")

write.table(data.frame(Description=c("","","","Results:")),row.names=F,col.names=F,quote=F)
write.table(data.frame(Description=c("","Table A1: Lobbyist Compensation Formats in FARA Filings (Exhibits A&B)",
"Among High Earning Federal Lobbying Firms, 2017-2018"))
,row.names=F,col.names=F,quote=F)

#Results: Table A1 (ordering by income, except for summary row)
print(kbl(df[c(order(df[c(1:20),6],decreasing = T),21),],row.names = F,format="latex"))
print(kbl(df[c(order(df[c(1:20),6],decreasing = T),21),],row.names = F,format="html"))

write(kbl(df[c(order(df[c(1:20),6],decreasing = T),21),],row.names = F,format="latex"),"output/PSRM_tabA1.tex")
write.table(data.frame(Description=c("(N.B. The file for this table is saved as 'PSRM_tabA1.tex' in the 'output' folder.)")),row.names=F,col.names=F,quote=F)

# \begin{tabular}[t]{l|l|l|l|l|r}
# \hline
# Lobbying Firm & No. of Agreements & Flat Fees (Exc. Exp.) & Retainers & Hourly Rates & Total Income '17-'18 (LDA)\\
# \hline
# Akin Gump et al & 12 & 6 (2) & 5 & 2 & 76785000\\
# \hline
# Brownstein Hyatt et al & 4 & 4 (2) & 0 & 0 & 59965000\\
# \hline
# BGR Group & 11 & 11 (8) & 0 & 0 & 50894000\\
# \hline
# Squire Patton Boggs & 5 & 3 (3) & 2 & 1 & 48517000\\
# \hline
# Holland \& Knight & 1 & 1 (0) & 0 & 0 & 46090000\\
# \hline
# Cornerstone Government Affairs & 4 & 4 (4) & 0 & 0 & 41010000\\
# \hline
# K\&L Gates & 4 & 3 (3) & 1 & 0 & 36020000\\
# \hline
# Capitol Counsel & 5 & 4 (0) & 1 & 0 & 35880000\\
# \hline
# Covington \& Burling & 0 & 0 (0) & 0 & 0 & 35018000\\
# \hline
# Van Scoyoc Assoc & 0 & 0 (0) & 0 & 0 & 33410000\\
# \hline
# Williams \& Jensen & 0 & 0 (0) & 0 & 0 & 32850000\\
# \hline
# Capitol Tax Partners & 0 & 0 (0) & 0 & 0 & 29140000\\
# \hline
# Mehlman Castagnetti et al & 0 & 0 (0) & 0 & 0 & 28745000\\
# \hline
# Peck Madigan Jones & 0 & 0 (0) & 0 & 0 & 28050000\\
# \hline
# Cassidy \& Assoc & 1 & 1 (1) & 0 & 0 & 28040000\\
# \hline
# Fierce Government Relations & 0 & 0 (0) & 0 & 0 & 26380000\\
# \hline
# American Continental Group & 1 & 1 (1) & 0 & 0 & 26100000\\
# \hline
# Podesta Group & 13 & 13 (7) & 0 & 0 & 18410000\\
# \hline
# Ballard Partners & 14 & 14 (11) & 0 & 0 & 18140000\\
# \hline
# Ernst \& Young & 0 & 0 (0) & 0 & 0 & 15160000\\
# \hline
# Total & 75 & 65 (42) & 9 & 3 & 714604000\\
# \hline
# \end{tabular}